
package com.rx.mgt.dao;  

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.rx.mgt.dao.base.ManagementHibernateDaoBase;
import com.rx.mgt.domain.UserInfoExt;
import com.rx.mgt.po.UserGroupCountBo;
import com.rx.mgt.po.UsersBo;

/** 
 * ClassName:UserInfoExtDao 
 * Function :
 * Reason   : 
 * Date     :Jan 31, 2017 9:21:30 PM 
 * @author   luolei 
 * @since    JDK 1.6
 */
@Repository
public class UserInfoExtDao extends ManagementHibernateDaoBase<UserInfoExt, Long>{
    @Transactional(readOnly=true)
    public Long getUserInfoExtCount(UsersBo usersBo){
        String hql= " select count(*) from UserInfoExt au where 1=1";
        if(StringUtils.isNotBlank(usersBo.getStartDate())){
            hql=hql +" and  unix_timestamp(au.create_time)>unix_timestamp(:startTime)";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(usersBo.getStartDate())){
            query.setParameter("startTime", usersBo.getStartDate());
        }
        return (Long) query.uniqueResult();
    }
    
    @Transactional(readOnly=true)
    public List<UserGroupCountBo> getAllUserGroupCount(){
        String queryString= " SELECT a.user_quota_group_id  as id ,COUNT(a.user_quota_group_id) as counts from user_info_ext  a where a.user_quota_group_id is not null "+
                    " GROUP BY a.user_quota_group_id ";
        Query query = template.getSessionFactory().getCurrentSession().createSQLQuery(queryString);
        query.setResultTransformer(new AliasToBeanResultTransformer(UserGroupCountBo.class));
        return query.list();
    }
	@Transactional(readOnly=true)
    public List getAllUser(UsersBo usersBo){
        String hql= " from UserInfoExt au where 1=1  ";
        if(StringUtils.isNotBlank(usersBo.getDomain())){
        	hql=hql +" and au.domain=:domain";
        }
        if(StringUtils.isNotBlank(usersBo.getUser_name())){
            hql=hql +" and au.user_name=:user_name";
        }
        if(StringUtils.isNotBlank(usersBo.getStartDate())){
            hql=hql +" and  unix_timestamp(au.create_time)>unix_timestamp(:startTime)";
        }
        if(StringUtils.isNotBlank(usersBo.getEndDate())){
            hql=hql +" and  unix_timestamp(au.create_time)<unix_timestamp(:endTime)";
        }
        if(StringUtils.isNotBlank(usersBo.getGjjAccount())){
            hql=hql +" and  au.gjjAccount like :gjjAccount";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(usersBo.getDomain())){
        	query.setParameter("domain", usersBo.getDomain());
        }
        if(StringUtils.isNotBlank(usersBo.getUser_name())){
            query.setParameter("user_name", usersBo.getUser_name());
        }
        if(StringUtils.isNotBlank(usersBo.getStartDate())){
            query.setParameter("startTime", usersBo.getStartDate());
        }
        if(StringUtils.isNotBlank(usersBo.getEndDate())){
            query.setParameter("endTime", usersBo.getEndDate());
        }
        if(StringUtils.isNotBlank(usersBo.getGjjAccount())){
            query.setParameter("gjjAccount", "%"+usersBo.getGjjAccount()+"%");
        }
        return query.list();
    }
    @Transactional(readOnly=true)
    public UserInfoExt getUserInfoByUserName(String userName) {
        String hql = "from UserInfoExt u where 1=1";
        if(StringUtils.isNotBlank(userName)){
            hql=hql+" and u.user_name=:userName";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(userName)){
            query.setParameter("userName",userName);
        }
        return (UserInfoExt)query.uniqueResult();
    }

    @Transactional(readOnly=true)
    public UserInfoExt getUserInfoByNickName(String nickName) {
        String hql = "from UserInfoExt u where 1=1";
        if(StringUtils.isNotBlank(nickName)){
            hql=hql+" and u.nike_name=:nickName ";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(nickName)){
            query.setParameter("nickName",nickName);
        }
        return (UserInfoExt)query.uniqueResult();
    }

    public List<UserInfoExt> queryGroupMembersByGroupId(String groupId) {
        String hql = " from UserInfoExt ue where 1=1";
        if(StringUtils.isNotBlank(groupId)){
            hql +=" and group_id in(:ids) ";
        }
        List<BigInteger> list = new ArrayList<BigInteger>();
        Query query = template.getSessionFactory().getCurrentSession().createSQLQuery("SELECT id FROM rx_group WHERE id=? or pid =?").setParameter(0,Long.valueOf(groupId)).setParameter(1,Long.valueOf(groupId));
        list =  query.list();
        List<Long> ids = new ArrayList<Long>();
        for(BigInteger b:list){
            ids.add(b.longValue());
        }
        Query q2 = template.getSessionFactory().getCurrentSession().createQuery(hql).setParameterList("ids",ids);
        return  q2.list();
    }

    public UserInfoExt getUserByDomainAndEmail(String domian, String email) {
        String hql = " from UserInfoExt ex where 1=1";
        if(StringUtils.isNotBlank(domian)){
            hql +=" and ex.domain=:domain";
        }
        if (StringUtils.isNotBlank(email)){
            hql +=" and ex.user_name=:email";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if (StringUtils.isNotBlank(domian)) {
            query.setParameter("domain",domian);
        }
        if(StringUtils.isNotBlank(email)){
            query.setParameter("email",email);
        }
        return (UserInfoExt) query.uniqueResult();

    }

    public List<UserInfoExt> getAllUserHasNikeName(UsersBo usersBo) {
        String hql = " from UserInfoExt ex where 1=1 and ex.nike_name is not null";
        if(StringUtils.isNotBlank(usersBo.getDomain())){
            hql +=" and domain=:domain";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(usersBo.getDomain())){
            query.setParameter("domain",usersBo.getDomain());
        }
        return query.list();
    }

    public Integer deleteEmailAlias(String id) {
        String sql = "update user_info_ext set nike_name=null where id=:id";
        Query query = template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setParameter("id", id);
        return  query.executeUpdate();
    }

    public List<UserInfoExt> getUserInfoByDomain(String domain) {
        String hql = "from UserInfoExt ex where 1=1 and ex.status='Y'";
        if(StringUtils.isNotBlank(domain)){
            hql +=" and ex.domain=:domain";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(domain)){
            query.setParameter("domain",domain);
        }
        return query.list();
    }
}
  